﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DiskGalleryDataAPI.DataAccesss
{
    public class DiskDataAccessor
    {
        public DataTable SelectAll()
        {
            ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["myDisksConnectionString"];
            SqlConnection connection = new SqlConnection(connectionString.ConnectionString);
            SqlCommand selectAll = new SqlCommand("selectAll", connection);
            selectAll.CommandType = CommandType.StoredProcedure;
            DataTable tbSelectAll = new DataTable();

            //класс, который будет заполнять DataTable
            SqlDataAdapter adapter = new SqlDataAdapter(selectAll);
            connection.Open();
            adapter.Fill(tbSelectAll);
            connection.Close();

            return tbSelectAll;
        }

        public DataRow SelectById(int id)
        {
            ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["myDisksConnectionString"];
            SqlConnection connection = new SqlConnection(connectionString.ConnectionString);
            SqlCommand selectById = new SqlCommand("selectById", connection);
            selectById.CommandType = CommandType.StoredProcedure;
            selectById.Parameters.AddWithValue("disk_id", id);
            DataTable tbSelectById = new DataTable();

            //класс, который будет заполнять DataTable
            SqlDataAdapter adapter = new SqlDataAdapter(selectById);
            connection.Open();
            adapter.Fill(tbSelectById);
            connection.Close();

            return tbSelectById.Rows[0];
        }


        public void InsertNew(string label, string author, string contentName, string content, string dateTime, string size, string iconName)
        {
            ConnectionStringSettings connectionString =
                ConfigurationManager.ConnectionStrings["myDisksConnectionString"];
            SqlConnection connection = new SqlConnection(connectionString.ConnectionString);
            SqlCommand insertNew = new SqlCommand("insertNew", connection);

            insertNew.Parameters.AddWithValue("disk_label", label);
            insertNew.Parameters.AddWithValue("disk_author", author);
            insertNew.Parameters.AddWithValue("disk_content_name", contentName);
            insertNew.Parameters.AddWithValue("disk_content", content);
            insertNew.Parameters.AddWithValue("disk_date_create", dateTime);
            insertNew.Parameters.AddWithValue("disk_size", size);
            insertNew.Parameters.AddWithValue("disk_icon_name", iconName);
            insertNew.CommandType = CommandType.StoredProcedure;

            connection.Open();
            insertNew.ExecuteNonQuery();
            connection.Close();
        }


        public void UpdateById(int id, string label, string author, string contentName, string content, string dateTime, string size, string iconName)
        {
            ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["myDisksConnectionString"];
            SqlConnection connection = new SqlConnection(connectionString.ConnectionString);
            SqlCommand updateById = new SqlCommand("updateById", connection);

            updateById.Parameters.AddWithValue("disk_id", id);
            updateById.Parameters.AddWithValue("disk_label", label);
            updateById.Parameters.AddWithValue("disk_author", author);
            updateById.Parameters.AddWithValue("disk_content_name", contentName);
            updateById.Parameters.AddWithValue("disk_content", content);
            updateById.Parameters.AddWithValue("disk_date_create", dateTime);
            updateById.Parameters.AddWithValue("disk_size", size);
            updateById.Parameters.AddWithValue("disk_icon_name", iconName);
            updateById.CommandType = CommandType.StoredProcedure;

            connection.Open();
            updateById.ExecuteNonQuery();
            connection.Close();

        }

        public void DeleteById(int id)
        {
            ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["myDisksConnectionString"];
            SqlConnection connection = new SqlConnection(connectionString.ConnectionString);
            SqlCommand deleteById = new SqlCommand("deleteById", connection);
            deleteById.CommandType = CommandType.StoredProcedure;
            deleteById.Parameters.AddWithValue("disk_id", id);

            connection.Open();
            deleteById.ExecuteNonQuery();
            connection.Close();
        }

    }
}
